#!/usr/bin/env python3
# -*- coding: utf-8 -*-

from .db import con

def istable(name):
    cur = con.cursor()
    sql = ('''
    select count(*) as 'count' from sqlite_master where type ='table' and name = '%s'
    ''' % name)
    cur.execute(sql)
    for row in cur:
        print(row[0])
    # con.close()
def droptable(name):
    cur = con.cursor()
    sql = (""" drop table if exists %s """ % name)
    cur.execute(sql)
   
    # con.close()


def create_stock_basic():
    cur = con.cursor()
    sql = '''
    CREATE TABLE IF NOT EXISTS "stockbasic" (
                "code"    TEXT NOT NULL,
                "name"    TEXT NOT NULL,
                "area"    TEXT NOT NULL,
                "industry"    TEXT NOT NULL,
                "market"    TEXT NOT NULL,
                "changeTime"    TEXT,
                PRIMARY KEY("code","name")
            );
    '''
    cur.execute(sql)
    # con.close()

def create_etf_basic():
    cur = con.cursor()
    sql = '''
    CREATE TABLE IF NOT EXISTS "etfbasic" (
                "code"    TEXT NOT NULL,
                "name"    TEXT NOT NULL,
                "changeTime"    TEXT,
                PRIMARY KEY("code","name")
            );
    '''
    cur.execute(sql)
    # con.close()

def create_damrey():
    cur = con.cursor()
    sql = '''
    CREATE TABLE IF NOT EXISTS "stockma" -- 股票均线
     (
        "date" TEXT, -- 时间
        "code" TEXT, -- 股票代码
        "ma5" NUMERIC, -- 开盘价
        "ma10" NUMERIC, -- 收盘价
        "ma20" NUMERIC, -- 最高价
        "ma30" NUMERIC, -- 最低价
        "ma60" NUMERIC, -- 交易量
        PRIMARY KEY("code","date")
     )
    '''
    cur.execute(sql)
    # con.close()
def create_stockdaily():
    cur = con.cursor()
    sql = '''
    
    CREATE TABLE IF NOT EXISTS "stockdaily" -- 股票日数据
     (
        "date" TEXT, -- 时间
        "code" TEXT, -- 股票代码
        "open" NUMERIC, -- 开盘价
        "close" NUMERIC, -- 收盘价
        "high" NUMERIC, -- 最高价
        "low" NUMERIC, -- 最低价
        "vol" NUMERIC, -- 交易量
        "amount" NUMERIC, -- 交易价
        PRIMARY KEY("code","date")
     )
    '''
    cur.execute(sql)

def create_etfdaily():
    cur = con.cursor()
    sql = '''
    CREATE TABLE IF NOT EXISTS "etfdaily" -- 股票日数据
     (
        "date" TEXT, -- 时间
        "code" TEXT, -- 股票代码
        "open" NUMERIC, -- 开盘价
        "close" NUMERIC, -- 收盘价
        "high" NUMERIC, -- 最高价
        "low" NUMERIC, -- 最低价
        "vol" NUMERIC, -- 交易量
        PRIMARY KEY("code","date")
     )
    '''
    cur.execute(sql)
def create_damreystock():
    cur = con.cursor()
    sql = '''
        CREATE TABLE IF NOT EXISTS "damreystock" (
        "code"	TEXT,
        "date"	TEXT,
        "ma5"	NUMERIC,
        "ma10"	NUMERIC,
        "ma20"	NUMERIC,
        "ma30"	NUMERIC,
        "ma60"	NUMERIC,
        PRIMARY KEY("code","date")
    );
    '''
    cur.execute(sql)

def create_kirogistock():
    cur = con.cursor()
    sql = '''
        CREATE TABLE IF NOT EXISTS "kirogistock" (
        "code"	TEXT,
        "date"	TEXT,
        "speed5"	NUMERIC,
        "speed10"	NUMERIC,
        "speed20"	NUMERIC,
        "speed30"	NUMERIC,
        "speed60"	NUMERIC,
        PRIMARY KEY("code","date")
    );
    '''
    cur.execute(sql)
def create_damreyetf():
    cur = con.cursor()
    sql = '''
        CREATE TABLE IF NOT EXISTS "damreyetf" (
        "code"	TEXT,
        "date"	TEXT,
        "ma5"	NUMERIC,
        "ma10"	NUMERIC,
        "ma20"	NUMERIC,
        "ma30"	NUMERIC,
        "ma60"	NUMERIC,
        PRIMARY KEY("code","date")
    );
    '''
    cur.execute(sql)

def create_kirogietf():
    cur = con.cursor()
    sql = '''
        CREATE TABLE IF NOT EXISTS "kirogietf" (
        "code"	TEXT,
        "date"	TEXT,
        "speed5"	NUMERIC,
        "speed10"	NUMERIC,
        "speed20"	NUMERIC,
        "speed30"	NUMERIC,
        "speed60"	NUMERIC,
        PRIMARY KEY("code","date")
    );
    '''
    cur.execute(sql)

def create_rel_transaction():
    cur = con.cursor()
    sql = '''
     CREATE TABLE  IF NOT EXISTS "rel_transaction" ( -- 真实交易表
	"id"	INTEGER NOT NULL UNIQUE,
	"userid"	INT NOT NULL, -- 用户名
    "code"	TEXT NOT NULL, -- 代码
    "bdate"	TEXT NOT NULL, -- 买入时间
    "bprice" NUMERIC NOT NULL, -- 买入价格
    "bfree"	NUMERIC, --买入手续费
    "sdate"	TEXT, -- 买出时间
    "sprice" NUMERIC,  -- 买出价格
    "sfree"	NUMERIC, -- 买出手续费
    "plan"	TEXT, -- 方案,
    "remarks",TEXT,-- 备注
	PRIMARY KEY("id" AUTOINCREMENT)
);
    '''
    cur.execute(sql)

def create_user():
    cur = con.cursor()
    sql = '''
        CREATE TABLE IF NOT EXISTS "user" (
	    "id"	INTEGER UNIQUE,
	    "name"	TEXT NOT NULL UNIQUE,
	    "psw"	TEXT NOT NULL,
	    PRIMARY KEY("id" AUTOINCREMENT)
    );
    '''
    cur.execute(sql)

def create_etffollow():
    cur = con.cursor()
    sql = '''
       CREATE TABLE "etffollow" (
        "code"	TEXT NOT NULL UNIQUE, --编码
        "follow"	INTEGER NOT NULL, -- 是否关注
        "status"	INTEGER NOT NULL, -- 1->忽略 
        "userId"	INTEGER, -- 用户
        PRIMARY KEY("code")
    );
    '''
    cur.execute(sql)
def create_follow():
    cur = con.cursor()
    sql = '''
       CREATE TABLE "stockfollow" (
        "code"	TEXT NOT NULL UNIQUE, --编码
        "follow"	INTEGER NOT NULL, -- 是否关注
        "status"	INTEGER NOT NULL, -- 1->忽略 
        "userId"	INTEGER, -- 用户
        PRIMARY KEY("code")
    );
    '''
    cur.execute(sql)


create_stock_basic()
create_etf_basic()
create_damrey()
create_stockdaily()
create_etfdaily()
create_kirogistock()
create_damreystock()
create_kirogietf()
create_damreyetf()
create_rel_transaction()
create_user()


